﻿using zijian666.Data.SqlBuilders;

namespace zijian666.Data;

/// <summary>
/// 扩展方法
/// </summary>
public static partial class DbConnectionExtensions
{
    /// <summary>
    /// 查询列表时的默认的最大返回行数
    /// </summary>
    public const int DEFAULT_LIMIT = 100000;

    /// <summary>
    /// 释放组件
    /// </summary>
    /// <param name="connectionOnlyClose"> 如果 <paramref name="compoent"/> 是 <seealso cref="IDbConnection"/>,则 <c>true</c> 表示仅关闭连接, 不释放, 否则为 <c>false</c> </param>
    internal static void ComponentDispose(this object compoent, bool connectionOnlyClose = true)
    {
        if (compoent is null)
        {
            return;
        }
        try
        {
            switch (compoent)
            {
                case IDbConnection connection:
                    if (connection.IsOpen())
                    {
                        connection.SafeClose();
                    }
                    if (!connectionOnlyClose)
                    {
                        connection.Dispose();
                    }
                    break;
                case IDataReader reader:
                    if (!reader.IsClosed)
                    {
                        reader.Close();
                    }
                    reader.Dispose();
                    break;
                case IDisposable disposable:
                    disposable.Dispose();
                    break;
                case IAsyncDisposable disposable:
                    {
                        var task = disposable.DisposeAsync();
                        if (!task.IsCompleted)
                        {
                            task.AsTask().Wait();
                        }
                    }
                    break;
                case Action disposable:
                    disposable();
                    break;
                case Func<Task> disposable:
                    {
                        var task = disposable();
                        if (!task.IsCompleted)
                        {
                            task.Wait();
                        }
                    }
                    break;
                default:
                    break;
            }
        }
        catch (Exception e)
        {
            Diagnostic.Error(e);
        }
    }

    /// <summary>
    /// 如果数据库连接当前为打开状态, 则返回 <code>true</code>, 否则返回 <code>false</code>
    /// </summary>
    public static bool IsOpen([NotNullWhen(true)] this IDbConnection? conn)
        => conn?.State is ConnectionState.Open;

    /// <summary>
    /// 如果数据库连接当前不是打开状态, 则返回 <code>true</code>, 否则返回 <code>false</code>
    /// </summary>
    public static bool NotOpen([NotNullWhen(true)] this IDbConnection? conn)
        => conn?.State is not ConnectionState.Open;


    /// <summary>
    /// 如果数据库连接当前为关闭状态, 则返回 <code>true</code>, 否则返回 <code>false</code>
    /// </summary>
    public static bool IsClosed([NotNullWhen(true)] this IDbConnection? conn)
        => conn?.State is ConnectionState.Closed;

    /// <summary>
    /// 如果数据库连接当前不是关闭状态, 则返回 <code>true</code>, 否则返回 <code>false</code>
    /// </summary>
    public static bool NotClosed([NotNullWhen(true)] this IDbConnection? conn)
        => conn?.State is not ConnectionState.Closed;

    /// <summary>
    /// 如果数据库连接当前为关闭状态, 则打开返回 <code>true</code>, 否则返回 <code>false</code>
    /// <para> 如果 <paramref name="conn"/> 为null, 也返回 <code>false</code> </para>
    /// </summary>
    /// <param name="conn"></param>
    public static bool OpenIfClosed([NotNullWhen(true)] this IDbConnection? conn)
    {
        if (conn is null)
        {
            return false;
        }
        var timestamp = Stopwatch.GetTimestamp();
        try
        {
            if (NotOpen(conn))
            {
                var state = conn.State;
                conn.Open();
                _ = DbInterceptors.OnConnectionStateChanging(() => new(conn, state, timestamp, CancellationToken.None)).GetAwaiter().GetResult();
                return true;
            }
            return false;
        }
        catch (Exception ex)
        {
            _ = DbInterceptors.OnExceptionOccurred(() => new(conn, ex, timestamp, CancellationToken.None)).GetAwaiter().GetResult();
            throw;
        }
    }

    /// <summary>
    /// 关闭数据库连接, 不返回任何错误
    /// </summary>
    /// <param name="conn"></param>
    public static void SafeClose(this IDbConnection? conn)
    {
        if (conn is null)
        {
            return;
        }
        var timestamp = Stopwatch.GetTimestamp();
        try
        {
            if (NotClosed(conn))
            {
                var state = conn.State;
                conn.Close();
                _ = DbInterceptors.OnConnectionStateChanging(() => new(conn, state, timestamp, CancellationToken.None)).GetAwaiter().GetResult();
            }
        }
        catch (Exception ex)
        {
            _ = DbInterceptors.OnExceptionOccurred(() => new(conn, ex, timestamp, CancellationToken.None)).GetAwaiter().GetResult();
        }
    }

    /// <summary>
    /// 在 Scope 范围内, 第一次打开数据库连接后, 会保持打开状态, 直到最外层的 <see cref="IDisposable"/> 对象, 在 <see cref="IDisposable.Dispose"/> 时关闭数据库连接
    /// </summary>
    /// <param name="conn"></param>
    /// <param name="dispose">scope结束后是否释放连接</param>
    /// <returns></returns>
    public static ConnectionScope OpenScope(this IDbConnection conn, bool dispose = false) => new ConnectionScope(conn, dispose);

    /// <summary>
    /// 使用 sql语句(<paramref name="sql"/>) 创建数据库执行命令(<see cref="IDbCommand"/>)
    /// </summary>
    public static IDbCommand CreateCommand(this IDbConnection conn, FormattableString sql)
        => CreateCommand(conn, sql, (FormattableString[]?)null);

    public static IDbCommand CreateCommand(this IDbConnection conn, params FormattableString[] sqls)
        => CreateCommand(conn, null, sqls);

    /// <summary>
    /// 使用 sql语句(<paramref name="sql"/>) 创建数据库执行命令(<see cref="IDbCommand"/>)
    /// </summary>
    internal static IDbCommand CreateCommand(this IDbConnection conn, FormattableString? sql, FormattableString[]? sqls, Action<IDbCommand>? processCmd = null)
    {
        ArgumentNullException.ThrowIfNull(conn);

        if (string.IsNullOrWhiteSpace(sql?.Format) && (sqls is null or { Length: 0 }))
        {
            throw new ArgumentNullException(nameof(sql));
        }
        var timestamp = Stopwatch.GetTimestamp();
        var cmd = conn.CreateCommand();
        cmd.CommandType = CommandType.Text;
        cmd.Connection ??= conn;

        using (ObjectPoolHelper.GetDbParameterFormatProvider(out var provider))
        {
            provider.SetCommand(cmd);
            if (sql is not null)
            {
                var text = string.Format(provider, sql.Format, sql.GetArguments());
                cmd.CommandText = text;
            }
            if (sqls is { Length: > 0 })
            {
                if (cmd.CommandText?.Length > 0 && !cmd.CommandText.EndsWith(";"))
                {
                    cmd.CommandText += ";\n";
                }
                cmd.CommandText += string.Join(";\n", sqls.Select(x => string.Format(provider, x.Format, x.GetArguments()).Trim(';'))) + ";";
            }
        }
        processCmd?.Invoke(cmd);
        _ = DbInterceptors.OnCommandCreated(() => new(cmd, timestamp, CancellationToken.None)).GetAwaiter().GetResult();

        return cmd;
    }

    /// <summary>
    /// 将 <paramref name="value"/> 添加到 <paramref name="command"/>
    /// </summary>
    public static IDbDataParameter AddParameter(this IDbCommand command, object value)
    {
        ArgumentNullException.ThrowIfNull(command);

        var p = command.CreateParameter();
        p.Value = value.GetDbObject();
        command.Parameters.Add(p);
        return p;
    }

    /// <summary>
    /// 执行委托 <paramref name="func"/>
    /// <para> 如果数据库连接未打开, 则打开, 执行完毕后关闭连接 </para>
    /// <para> 如果数据库连接已打开则直接执行委托, 执行完毕后也不关闭连接 </para>
    /// </summary>
    public static T? Execute<T>(this IDbConnection conn, Func<ConnectionScope, T> func)
    {
        ArgumentNullException.ThrowIfNull(func);
        using var opening = OpenScope(conn);
        try
        {
            conn.OpenIfClosed();
            if (System.Transactions.Transaction.Current is { } trans && conn is DbConnection db)
            {
                db.EnlistTransaction(trans);
            }
            return func(opening);
        }
        catch (Exception ex)
        {
            _ = DbInterceptors.OnExceptionOccurred(() => new(conn, ex, opening.Timestamp, CancellationToken.None) { Scope = opening }).GetAwaiter().GetResult();
            throw;
        }
    }

    /// <summary>
    /// 执行委托 <paramref name="action"/>
    /// <para> 如果数据库连接未打开, 则打开, 执行完毕后关闭连接 </para>
    /// <para> 如果数据库连接已打开则直接执行委托, 执行完毕后也不关闭连接 </para>
    /// </summary>
    public static void Execute(this IDbConnection conn, Action<ConnectionScope> action)
    {
        if (action is null)
        {
            return;
        }
        using var opening = OpenScope(conn);
        try
        {
            conn.OpenIfClosed();
            if (System.Transactions.Transaction.Current is { } trans && conn is DbConnection db)
            {
                db.EnlistTransaction(trans);
            }
            action(opening);
        }
        catch (Exception ex)
        {
            _ = DbInterceptors.OnExceptionOccurred(() => new(conn, ex, opening.Timestamp, CancellationToken.None) { Scope = opening }).GetAwaiter().GetResult();
            throw;
        }
    }

    /// <summary>
    /// 执行事务, 除非 <paramref name="action"/> 抛出异常, 否则直接提交
    /// <para> 如果数据库连接未打开, 则打开, 执行完毕后关闭连接 </para>
    /// <para> 如果数据库连接已打开则直接执行委托, 执行完毕后也不关闭连接 </para>
    /// </summary>
    /// <param name="conn"></param>
    /// <param name="action"></param>
    public static void Transaction(this IDbConnection conn, Action action)
    {
        if (action is null)
        {
            return;
        }

        Execute(conn, opening =>
        {
            using var tran = new TransactionScope();
            opening.Add(tran);
            action();
            tran.Complete();
        });
    }

    /// <summary>
    /// 执行事务, 除非 <paramref name="func"/> 抛出异常, 否则直接提交
    /// <para> 如果数据库连接未打开, 则打开, 执行完毕后关闭连接 </para>
    /// <para> 如果数据库连接已打开则直接执行委托, 执行完毕后也不关闭连接 </para>
    /// </summary>
    /// <param name="conn"></param>
    /// <param name="func"></param>
    public static T? Transaction<T>(this IDbConnection conn, Func<T> func)
    {
        ArgumentNullException.ThrowIfNull(func);

        return Execute(conn, opening =>
        {
            using var tran = new TransactionScope();
            opening.Add(tran);
            var result = func();
            tran.Complete();
            return result;
        });
    }


    /// <summary>
    /// 执行sql, 处理 <see cref="IDataReader"/>
    /// </summary>
    public static void ExecuteReader(this IDbConnection conn, FormattableString sql, Action<IDataReader> action)
    {
        ArgumentNullException.ThrowIfNull(sql);
        ArgumentNullException.ThrowIfNull(action);

        Execute(conn, opening =>
        {
            var command = CreateCommand(conn, sql);
            opening.Add(command);
            ExecuteReader(command, action);
        });
    }

    /// <summary>
    /// 执行 <see cref="IDbCommand"/>, 处理 <see cref="IDataReader"/>
    /// </summary>
    public static void ExecuteReader(this IDbCommand command, Action<IDataReader> action)
    {
        ArgumentNullException.ThrowIfNull(command);
        ArgumentNullException.ThrowIfNull(action);
        var timestamp = Stopwatch.GetTimestamp();
        var reader = command.ExecuteReader();
        using var reading = DataReaderScope.Create(reader, command, timestamp, CancellationToken.None).GetAwaiter().GetResult();
        action(reader);
    }

    /// <summary>
    /// 执行sql, 处理 <see cref="IDataReader"/>, 并返回数据
    /// </summary>
    public static T? ExecuteReader<T>(this IDbConnection conn, FormattableString sql, Func<IDataReader, T> func)
    {
        ArgumentNullException.ThrowIfNull(sql);
        ArgumentNullException.ThrowIfNull(func);

        return Execute(conn, opening =>
        {
            using var command = CreateCommand(conn, sql);
            opening.Add(command);
            return ExecuteReader(command, func);
        });
    }

    /// <summary>
    /// 执行<see cref="IDbCommand"/>, 处理 <see cref="IDataReader"/>, 并返回数据
    /// </summary>
    public static T? ExecuteReader<T>(this IDbCommand command, Func<IDataReader, T> func)
    {
        ArgumentNullException.ThrowIfNull(command);
        ArgumentNullException.ThrowIfNull(func);

        var timestamp = Stopwatch.GetTimestamp();
        var reader = command.ExecuteReader();
        using var reading = DataReaderScope.Create(reader, command, timestamp, CancellationToken.None).GetAwaiter().GetResult();
        var result = func(reader);
        reading.SetResult(result);
        return result;
    }

    /// <summary>
    /// 执行查询命令返回受影响行数
    /// </summary>
    /// <returns></returns>
    public static int ExecuteNonQuery(this IDbConnection conn, FormattableString sql)
        => ExecuteNonQuery(conn, sql, []);

    /// <summary>
    /// 执行查询命令返回受影响行数
    /// </summary>
    /// <returns></returns>
    public static int ExecuteNonQuery(this IDbConnection conn, params FormattableString[] sqls)
        => ExecuteNonQuery(conn, null, sqls);

    /// <summary>
    /// 执行查询命令返回受影响行数
    /// </summary>
    /// <returns></returns>
    private static int ExecuteNonQuery(this IDbConnection conn, FormattableString? sql, params FormattableString[]? sqls)
    {
        return Execute(conn, opening =>
        {
            var timestamp = Stopwatch.GetTimestamp();
            if (sqls?.Length > 1 && conn is DbConnection conn2)
            {
                var provider = DbProviderFactories.GetFactory(conn2);
                if (provider?.CanCreateBatch is true)
                {
                    var batch = provider.CreateBatch();
                    opening.Add(batch);
                    batch.Connection = conn2;
                    foreach (var item in sqls)
                    {
                        using var command = CreateCommand(conn, item);
                        var cmd = batch.CreateBatchCommand();
                        cmd.CommandText = command.CommandText;
                        cmd.CommandType = command.CommandType;
                        var array = new DbParameter[command.Parameters.Count];
                        command.Parameters.CopyTo(array, 0);
                        cmd.Parameters.AddRange(array);
                        batch.BatchCommands.Add(cmd);
                    }

                    var result = batch.ExecuteNonQuery();
                    _ = DbInterceptors.OnNonQueryExecuted(() => new(batch, timestamp, result, CancellationToken.None)).GetAwaiter().GetResult();
                    return result;
                }
            }

            {
                using var command = CreateCommand(conn, sql, sqls);
                opening.Add(command);
                var result = command.ExecuteNonQuery();
                _ = DbInterceptors.OnNonQueryExecuted(() => new(command, timestamp, result, CancellationToken.None)).GetAwaiter().GetResult();
                return result;
            }

        });
    }



    /// <summary>
    /// 执行sql, 返回实体类集合
    /// </summary>
    public static List<T> ExecuteList<T>(this IDbConnection conn, FormattableString sql, int limit = DEFAULT_LIMIT)
        => ExecuteReader(conn, sql, reader => reader.ReadList<T>(limit));

    /// <summary>
    /// 执行 <see cref="IDbCommand"/>, 返回实体类集合
    /// </summary>
    public static List<T> ExecuteList<T>(this IDbCommand command, int limit = DEFAULT_LIMIT)
        => ExecuteReader(command, reader => reader.ReadList<T>(limit));

    /// <summary>
    /// 读取 <see cref="IDataReader"/> 中的数据，并返回动态集合 <see cref="List{dynamic}"/>
    /// </summary>
    public static List<T> ReadList<T>(this IDataReader reader, int limit = DEFAULT_LIMIT)
    {
        if (typeof(object) == typeof(T))
        {
            return (List<T>)(object)reader.ReadList(limit);
        }

        return EntityBuilder.GetBuilder<T>().ToMultiple(reader).Take(limit).ToList();
    }

    /// <summary>
    /// 执行sql, 返回动态对象集合
    /// </summary>
    public static List<dynamic> ExecuteList(this IDbConnection conn, FormattableString sql, int limit = DEFAULT_LIMIT)
        => ExecuteReader(conn, sql, reader => reader.ReadList(limit));

    /// <summary>
    /// 执行 <see cref="IDbCommand"/>, 返回动态对象集合
    /// </summary>
    public static List<dynamic> ExecuteList(this IDbCommand command, int limit = DEFAULT_LIMIT)
        => ExecuteReader(command, reader => reader.ReadList(limit));

    /// <summary>
    /// 读取 <see cref="IDataReader"/> 中的数据，并返回动态集合 <see cref="List{dynamic}"/>
    /// </summary>
    /// <param name="reader"></param>
    /// <param name="limit"></param>
    /// <returns></returns>
    public static List<dynamic> ReadList(this IDataReader reader, int limit = DEFAULT_LIMIT)
    {
        var mapping = GetFieldMapping(reader);
        var list = new List<dynamic>();
        for (var i = 0; i < limit && reader.Read(); i++)
        {
            var values = new object[reader.FieldCount];
            reader.GetValues(values);
            list.Add(new DynamicRecord(mapping, values));
        }
        return list;
    }

    /// <summary>
    /// 获取 <see cref="IDataReader"/> 中字段
    /// </summary>
    /// <returns></returns>
    public static Dictionary<string, int> GetFieldMapping(this IDataReader reader)
    {
        ArgumentNullException.ThrowIfNull(reader);
        var fieldCount = reader.FieldCount;
        var mapping = new Dictionary<string, int>(fieldCount, StringComparer.OrdinalIgnoreCase);
        for (var i = 0; i < fieldCount; i++)
        {
            mapping.Add(reader.GetName(i), i);
        }
        return mapping;
    }

    /// <summary>
    /// 执行sql, 返回第一行数据的实体类型对象
    /// </summary>
    public static T? ExecuteFirst<T>(this IDbConnection conn, FormattableString sql)
    {
        return Execute(conn, opening =>
        {
            using var command = conn.PagingCommand(sql, 0, 1);
            opening.Add(command);
            return ExecuteFirst<T>(command);
        });
    }

    /// <summary>
    /// 执行 <see cref="IDbCommand"/>, 返回第一行数据的实体类型对象
    /// </summary>
    public static T? ExecuteFirst<T>(this IDbCommand command)
        => ExecuteReader(command, reader => reader.Read() ? EntityBuilder.GetBuilder<T>().ToSingle(reader) : default);

    /// <summary>
    /// 读取 <see cref="IDataReader"/> 中下一行的数据，并返回实体类型对象
    /// </summary>]
    /// <returns></returns>
    public static T? ReadSingle<T>(this IDataReader reader)
    {
        ArgumentNullException.ThrowIfNull(reader);
        return reader.Read() ? ToSingle<T>(reader) : default;
    }

    /// <summary>
    /// 读取 <see cref="IDataReader"/> 中当前行的数据，并返回实体类型对象
    /// </summary>]
    /// <returns></returns>
    public static T? ToSingle<T>(this IDataReader reader)
        => EntityBuilder.GetBuilder<T>().ToSingle(reader);

    /// <summary>
    /// 执行sql, 返回第一行数据的动态对象
    /// </summary>
    /// <returns></returns>
    public static dynamic ExecuteFirst(this IDbConnection conn, FormattableString sql)
    {
        return Execute(conn, opening =>
        {
            using var command = conn.PagingCommand(sql, 0, 1);
            opening.Add(command);
            return ExecuteFirst(command);
        });
    }

    /// <summary>
    /// 执行 <see cref="IDbCommand"/>, 返回第一行数据的动态对象
    /// </summary>
    public static dynamic ExecuteFirst(this IDbCommand command)
        => ExecuteReader(command, reader => reader.ReadSingle());


    /// <summary>
    /// 读取 <see cref="IDataReader"/> 中下一行的数据，并返回动态类型对象
    /// </summary>]
    /// <returns></returns>
    public static dynamic ReadSingle(this IDataReader reader)
    {
        ArgumentNullException.ThrowIfNull(reader);
        return reader.Read() ? ToSingle(reader) : null;
    }

    /// <summary>
    /// 读取 <see cref="IDataReader"/> 中当前行的数据，并返回动态类型对象
    /// </summary>]
    /// <returns></returns>
    public static dynamic ToSingle(this IDataReader reader)
    {
        var mapping = GetFieldMapping(reader);
        var values = new object[reader.FieldCount];
        reader.GetValues(values);
        return new DynamicRecord(mapping, values);
    }


    /// <summary>
    /// 执行sql, 返回第一行第一列的数据
    /// </summary>
    public static T? ExecuteScalar<T>(this IDbConnection conn, FormattableString sql, T? defaultValue = default)
        => ExecuteReader(conn, sql, reader => reader.ReadScalar(defaultValue));

    /// <summary>
    /// 执行 <see cref="IDbCommand"/>, 返回第一行第一列的数据
    /// </summary>
    public static T? ExecuteScalar<T>(this IDbCommand command, T? defaultValue = default)
        => ExecuteReader(command, reader => reader.ReadScalar(defaultValue));

    /// <summary>
    /// 读取 <see cref="IDataReader"/> 中下一行第一列的数据
    /// </summary>
    public static T? ReadScalar<T>(this IDataReader reader, T? defaultValue = default)
    {
        ArgumentNullException.ThrowIfNull(reader);
        return reader.Read() ? ToScalar<T>(reader) : defaultValue;
    }

    /// <summary>
    /// 读取 <see cref="IDataReader"/> 中当前行第一列的数据
    /// </summary>
    public static T? ToScalar<T>(this IDataReader reader)
        => EntityBuilder.Read<T>(reader, 0);


    /// <summary>
    /// 执行sql, 返回第一行第一列的动态对象
    /// </summary>
    public static dynamic ExecuteScalar(this IDbConnection conn, FormattableString sql, object? defaultValue = null)
        => ExecuteReader(conn, sql, reader => reader.ReadScalar(defaultValue));

    /// <summary>
    /// 执行 <see cref="IDbCommand"/>, 返回第一行第一列的动态对象
    /// </summary>
    public static dynamic ExecuteScalar(this IDbCommand command, object? defaultValue = null)
        => ExecuteReader(command, reader => reader.ReadScalar(defaultValue));

    /// <summary>
    /// 读取 <see cref="IDataReader"/> 中下一行第一列的动态对象
    /// </summary>
    public static dynamic ReadScalar(this IDataReader reader, object? defaultValue = null)
    {
        ArgumentNullException.ThrowIfNull(reader);
        return reader.Read() ? ToScalar(reader) : new DynamicAtom(defaultValue);
    }

    /// <summary>
    /// 读取 <see cref="IDataReader"/> 中当前行第一列的动态对象
    /// </summary>
    public static dynamic ToScalar(this IDataReader reader)
    {
        ArgumentNullException.ThrowIfNull(reader);
        return new DynamicAtom(reader.GetValue(0));
    }

    /// <summary>
    /// 执行sql, 返回 <see cref="DataTable"/>
    /// </summary>
    public static DataTable ExecuteDataTable(this IDbConnection conn, FormattableString sql, int limit = DEFAULT_LIMIT)
        => ExecuteReader(conn, sql, reader => reader.ReadDataTable(limit));

    /// <summary>
    /// 执行 <see cref="IDbCommand"/>, 返回 <see cref="DataTable"/>
    /// </summary>
    public static DataTable ExecuteDataTable(this IDbCommand command, int limit = DEFAULT_LIMIT)
        => ExecuteReader(command, reader => reader.ReadDataTable(limit));

    /// <summary>
    /// 读取 <see cref="IDataReader"/> 中的数据，并返回 <see cref="DataTable"/>
    /// </summary>
    public static DataTable ReadDataTable(this IDataReader reader, int limit = DEFAULT_LIMIT)
    {
        var table = ToDataTable(reader);
        var values = new object[reader.FieldCount];
        for (var i = 0; i < limit && reader.Read(); i++)
        {
            reader.GetValues(values);
            table.LoadDataRow(values, LoadOption.OverwriteChanges); // TODO: 我想知道1和3的区别
        }
        return table;
    }
    /// <summary>
    /// 读取 <see cref="IDataReader"/> 中的字段和类型，并返回没有数据的 <see cref="DataTable"/>
    /// </summary>
    public static DataTable ToDataTable(this IDataReader reader)
    {
        ArgumentNullException.ThrowIfNull(reader);
        var table = new DataTable();
        var fieldCount = reader.FieldCount;
        for (var i = 0; i < fieldCount; i++)
        {
            table.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
        }
        return table;
    }

    /// <summary>
    /// 执行sql, 返回 <see cref="DataRow"/>
    /// </summary>
    public static DataRow ExecuteDataRow(this IDbConnection conn, FormattableString sql)
        => ExecuteReader(conn, sql, reader => reader.ReadDataRow());

    /// <summary>
    /// 执行sql, 返回 <see cref="DataRow"/>
    /// </summary>
    public static DataRow ExecuteDataRow(this IDbCommand command)
        => ExecuteReader(command, reader => reader.ReadDataRow());

    /// <summary>
    /// 读取 <see cref="IDataReader"/> 中下一行
    /// </summary>
    public static DataRow ReadDataRow(this IDataReader reader)
    {
        ArgumentNullException.ThrowIfNull(reader);
        return reader.Read() ? ToDataRow(reader) : null;
    }

    /// <summary>
    /// 读取 <see cref="IDataReader"/> 中当前行
    /// </summary>
    public static DataRow ToDataRow(this IDataReader reader)
    {
        var table = ToDataTable(reader);
        var values = new object[reader.FieldCount];
        reader.GetValues(values);
        return table.LoadDataRow(values, LoadOption.OverwriteChanges);
    }


    /// <summary>
    /// 返回执行表名的表结构
    /// </summary>
    public static DataTable GetSchemaTable(this Type type)
    {
        ArgumentNullException.ThrowIfNull(type);

        var tableName = type.GetCustomAttribute<TableAttribute>();

        var schemaTable = new DataTable(tableName?.Name ?? type.Name, tableName?.Schema);

        var properties = type.GetProperties();

        foreach (var property in properties.OrderBy(x => x.GetCustomAttribute<ColumnAttribute>()?.Order ?? int.MaxValue))
        {
            if (property.IsDefined(typeof(NotMappedAttribute), true))
            {
                continue;
            }
            var columnName = property.GetCustomAttribute<ColumnAttribute>()?.Name ?? property.Name;
            var columnType = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;
            var schemaColumn = new DataColumn(columnName, columnType)
            {
                AllowDBNull = !property.IsDefined(typeof(RequiredAttribute)),
                MaxLength = property.GetCustomAttribute<StringLengthAttribute>()?.MaximumLength ?? -1,
                DefaultValue = property.GetCustomAttribute<DefaultValueAttribute>()?.Value,
                ReadOnly = property.GetCustomAttribute<ReadOnlyAttribute>()?.IsReadOnly ?? false,
                AutoIncrement = property.GetCustomAttribute<DatabaseGeneratedAttribute>()?.DatabaseGeneratedOption == DatabaseGeneratedOption.Identity,
                Caption = property.GetCustomAttribute<DisplayAttribute>()?.Name
                        ?? property.GetCustomAttribute<DescriptionAttribute>()?.Description
                        ?? property.Name
            };

            if (property.IsDefined(typeof(KeyAttribute)))
            {
                if (schemaTable.PrimaryKey is null or { Length: 0 })
                {
                    schemaTable.PrimaryKey = [schemaColumn];
                }
                else
                {
                    schemaTable.PrimaryKey = [.. schemaTable.PrimaryKey, schemaColumn];
                }
            }

            schemaTable.Columns.Add(schemaColumn);
        }

        return schemaTable;
    }

    /// <summary>
    /// 返回执行表名的表结构
    /// </summary>
    public static DataTable GetSchemaTable(this IDbConnection connection, string tableName)
    {
        ArgumentNullException.ThrowIfNull(connection);
        if (string.IsNullOrWhiteSpace(tableName))
        {
            throw new ArgumentException($"“{nameof(tableName)}”不能为 null 或空白。", nameof(tableName));
        }
        return connection.Execute(opening =>
        {
            var timestamp1 = Stopwatch.GetTimestamp();
            using var command = connection.CreateCommand();
            opening.Add(command);
            command.CommandText = $"SELECT * FROM {tableName} WHERE 1 = 2";
            command.CommandType = CommandType.Text;
            _ = DbInterceptors.OnCommandCreated(() => new(command, timestamp1, CancellationToken.None)).GetAwaiter().GetResult();
            var timestamp2 = Stopwatch.GetTimestamp();
            using var reader = command.ExecuteReader(CommandBehavior.SchemaOnly);
            opening.Add(reader);
            using var reading = DataReaderScope.Create(reader, command, timestamp2, CancellationToken.None).GetAwaiter().GetResult();
            var schemaTable = reader.GetSchemaTable() ?? throw new InvalidOperationException("无法获取表结构");
            schemaTable.TableName = tableName;
            return schemaTable;
        });
    }

    /// <summary>
    /// 将实体类集合<paramref name="data"/>填充到 <see cref="DataTable"/> <paramref name="table"/> 中
    /// </summary>
    public static DataTable FillData<T>(this DataTable table, IEnumerable<T> data)
    {
        ArgumentNullException.ThrowIfNull(table);
        ArgumentNullException.ThrowIfNull(data);
        //var properties = typeof(T).GetProperties().Where(x => table.Columns.Contains(x.Name)).ToArray();
        var columns = table.Columns;
        var scheam = EntityScheam.Get(typeof(T));
        var properties = scheam.Properties
            .Select(x => (x, Math.Max(columns.IndexOf(x.ColumnName), columns.IndexOf(x.PropertyInfo.Name))))
            .Where(x => x.Item2 >= 0)
            .ToArray();


        foreach (var item in data)
        {
            if (item is null)
            {
                continue;
            }
            var row = table.NewRow();

            foreach (var (property, index) in properties)
            {
                var value = property.GetValue(item);
                row[index] = value ?? DBNull.Value;
            }

            table.Rows.Add(row);
        }
        return table;
    }

    /// <summary>
    /// 按 <paramref name="schemaTable"/> 数据表结构创建空白表 <seealso cref="DataTable"/>
    /// </summary>
    public static DataTable BuildDataTable(this DataTable schemaTable)
    {
        ArgumentNullException.ThrowIfNull(schemaTable);
        var table = new DataTable(schemaTable.TableName);
        foreach (DataRow row in schemaTable.Rows)
        {
            var columnName = row.Field<string>("ColumnName");
            var columnType = row.Field<Type>("DataType");
            var column = new DataColumn(columnName, columnType ?? typeof(object))
            {
                AllowDBNull = row.Field<bool?>("AllowDBNull") ?? true,
                Unique = row.Field<bool?>("IsUnique") ?? false,
            };

            if (columnType == typeof(string))
            {
                column.MaxLength = row.Field<int?>("ColumnSize") ?? -1;
            }
            table.Columns.Add(column);
        }
        return table;
    }

    /// <summary>
    /// 获取数据库类型
    /// </summary>
    /// <exception cref="ArgumentNullException"></exception>
    public static DbProviderFactory GetDbProviderFactory(this IDbConnection connection)
    {
        ArgumentNullException.ThrowIfNull(connection);

        if (connection is DbConnection conn)
        {
            return DbProviderFactories.GetFactory(conn);
        }
        var name = connection.GetProviderInvariantName();
        return DbProviderFactories.GetFactory(name!);
    }

    /// <summary>
    /// 根据数据库连接获取 <see cref="ISqlBuilder"/> 对象
    /// </summary>
    /// <param name="connection"></param>
    /// <returns></returns>
    /// <exception cref="ArgumentNullException"></exception>
    public static ISqlBuilder GetSqlBuilder(this IDbConnection connection)
    {
        var name = connection.GetProviderInvariantName();
        if (name is null)
        {
            return connection.GetType().Name switch
            {
                "MySqlConnection" => MySqlBuilder.Instance,
                "NpgsqlConnection" => PgSqlBuilder.Instance,
                "SQLiteConnection" => SqliteBuilder.Instance,
                "OracleConnection" => OracleBuilder.Instance,
                "System.Data.SqlClient" => SqlServerBuilder.Instance,
                "Microsoft.Data.SqlClient" => SqlServerBuilder.Instance,
                "Microsoft.SqlServer.Server" => SqlServerBuilder.Instance,
                _ => throw new NotSupportedException()
            };
        }

        if (name.Contains("SqlServer", StringComparison.OrdinalIgnoreCase)
            || name.Contains("SqlClient", StringComparison.OrdinalIgnoreCase))
        {
            return SqlServerBuilder.Instance;
        }

        if (name.Contains("MySql", StringComparison.OrdinalIgnoreCase))
        {
            return MySqlBuilder.Instance;
        }

        if (name.Contains("PostgreSql", StringComparison.OrdinalIgnoreCase))
        {
            return PgSqlBuilder.Instance;
        }

        if (name.Contains("PgSql", StringComparison.OrdinalIgnoreCase))
        {
            return PgSqlBuilder.Instance;
        }

        if (name.Contains("Oracle", StringComparison.OrdinalIgnoreCase))
        {
            return OracleBuilder.Instance;
        }

        if (name.Contains("SQLite", StringComparison.OrdinalIgnoreCase))
        {
            return SqliteBuilder.Instance;
        }

        var factory = DbProviderFactories.GetFactory(name);
        return new CommonBuilder(factory);
    }
}
